/*
 * Copyright [2010] [GP Software]
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * PersonJdbcDao.java | Created on Mar 31, 2010
 */
package org.gp.dbtest.core.dao.jdbc;

import org.gp.dbtest.core.dao.PersonDao;
import org.gp.dbtest.core.model.Person;

import java.sql.*;

/**
 * JDBC implementation of Person DAO
 *
 * @author Paul Augustine C. Mendoza
 * @see org.gp.dbtest.core.dao.jdbc.PersonJdbcDao
 */
public class PersonJdbcDao implements PersonDao {

    /** The database url identifier */
    private static final String URL = "jdbc:mysql://localhost/dbtest";

    /** The database driver identifier */
    private static final String DRIVER = "com.mysql.jdbc.Driver";

    /** The user id */
    private static final String USERID = "root";

    /** The user password */
    private static final String PASSWORD = "root";

    /**
     * Gets a database connection
     *
     * @return the connection reference
     * @throws java.sql.SQLException if connection fails
     */
    private Connection getConnection() throws SQLException {
        Connection conn = null;
        try {
            Class.forName(DRIVER).newInstance();
            conn = DriverManager.getConnection(URL, USERID, PASSWORD);
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * Creates a person
     *
     * @param p the person to create
     * @throws SQLException if creating fails
     */
    public void create(Person p) throws SQLException {
        final String sql = "INSERT INTO PERSON VALUE(1,?,?,?,?)";
        Connection conn = null;
        PreparedStatement pStmt = null;
        try {
            conn = getConnection();
            pStmt = conn.prepareStatement(sql);
            pStmt.setString(1, p.getFirstname());
            pStmt.setString(2, p.getLastname());
            pStmt.setString(3, p.getEmail());
            pStmt.setInt(4, p.getAge());
            pStmt.execute();
        } finally {
            if (pStmt != null) {
                pStmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }

    /**
     * Retrieves a person
     *
     * @param id the id of the record to retrieve
     * @return the person retrieved
     * @throws SQLException
     */
    public Person getById(Integer id) throws SQLException {
        Person person = null;
        final String sql = "SELECT * FROM PERSON WHERE ID = ?";
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, id);
            rs = pStmt.executeQuery();
            if (rs.next()) {
                person = new Person(rs.getString("FIRSTNAME"),
                        rs.getString("LASTNAME"), rs.getString("EMAIL"),
                        rs.getInt("AGE"));
                person.setId(rs.getInt("ID"));
            }
        } finally {
            if (pStmt != null) {
                pStmt.close();
            }
            if (rs != null) {
                rs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return person;
    }

    /**
     * Updates a person's name
     *
     * @param id      the id of the person to update
     * @param newName the new value of name
     * @return the count of records updated
     * @throws SQLException if updated fails
     */
    public int updateNameById(Integer id, String newName) throws SQLException {
        final String sql = "UPDATE PERSON SET FIRSTNAME = ? WHERE ID = ?";
        PreparedStatement pStmt = null;
        Connection conn = null;
        int cnt = -1;
        try {
            conn = getConnection();
            pStmt = conn.prepareStatement(sql);
            pStmt.setString(1, newName);
            pStmt.setInt(2, id);
            cnt = pStmt.executeUpdate();
        } finally {
            if (pStmt != null) {
                pStmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return cnt;
    }

    /**
     * Deletes a person
     *
     * @param id the id of the record to delete
     * @return the count of records deleted
     * @throws SQLException if the delete fails
     */
    public int deleteById(Integer id) throws SQLException {
        final String sql = "DELETE FROM PERSON WHERE ID = ?";
        PreparedStatement pStmt = null;
        Connection conn = null;
        int cnt = -1;
        try {
            conn = getConnection();
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, id);
            cnt = pStmt.executeUpdate();
        } finally {
            if (pStmt != null) {
                pStmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return cnt;
    }
}
